package com.qidian.zl.paper.dao.impl;

import com.qidian.zl.paper.dao.BaseDAO;
import com.qidian.zl.paper.dao.PaperDAO;
import com.qidian.zl.paper.pojo.Paper;
import com.sun.org.apache.bcel.internal.generic.RET;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class PaperDAOImpl extends BaseDAO implements PaperDAO {
    private Connection con;
    private PreparedStatement pst;
    private ResultSet rs;

    @Override
    public Long save(Paper paper) {
        Long paperId = -1L;
        // paperStatus和papercount都是默认值
        String sql = "insert into paper(paper_title,paper_desc,paper_created,paper_status,paper_count,admin_id)" +
                " values(?,?,?,default,default,?)";
        try {
            con = getCon();
            con.setAutoCommit(false);// 开启事务
            pst = con.prepareStatement(sql);
            pst.setString(1, paper.getPaperTitle());
            pst.setString(2, paper.getPaperDesc());
            pst.setString(3, paper.getPaperCreated());
            pst.setLong(4,paper.getAdminId());
            pst.executeUpdate();
            // 获取当前事务中刚刚自动生成的id
            sql = "select LAST_INSERT_ID()";
            //再编译，在执行
            pst = con.prepareStatement(sql);
            rs = pst.executeQuery();
            if (rs.next()) {
                paperId = rs.getLong(1);
            }
            con.commit();
        } catch (Exception e) {
            e.printStackTrace();
            try {
                con.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            throw new RuntimeException(e);
        } finally {
            closeAll(rs, pst, con);
        }
        return paperId;
    }

    @Override
    public Long update(Paper paper) {
        String sql = "update paper set paper_title=?,paper_desc=? where paper_id=?";
        try {
            con = getCon();
            pst = con.prepareStatement(sql);
            pst.setString(1, paper.getPaperTitle());
            pst.setString(2, paper.getPaperDesc());
            pst.setLong(3, paper.getPaperId());
            pst.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            closeAll(rs, pst, con);
        }
        return paper.getPaperId();
    }

    //
    @Override
    public List<Paper> query(int page, int size) {
        List<Paper> list = new ArrayList<>();
        String sql = "select * from paper limit ?,?";

        try {
            con = getCon();
            pst = con.prepareStatement(sql);
            pst.setInt(1, (page - 1) * size);
            pst.setInt(2, size);
            rs = pst.executeQuery();
            while (rs.next()) {
                Paper paper = new Paper();
                paper.setPaperId(rs.getLong("paper_id"));
                paper.setPaperTitle(rs.getString("paper_title"));
                paper.setPaperCreated(rs.getString("paper_created"));
                paper.setPaperDesc(rs.getString("paper_desc"));
                paper.setPaperStatus(rs.getInt("paper_status"));
                paper.setPaperCount(rs.getInt("paper_count"));
                paper.setAdminId(rs.getLong("admin_id"));
                list.add(paper);
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            closeAll(rs, pst, con);
        }
        return list;
    }
    //
    //
    @Override
    public int queryTotal() {
        int result = -1;
        try {
            con = getCon();
            String sql = "select count(*) from paper";
            pst = con.prepareStatement(sql);
            rs = pst.executeQuery();
            if (rs.next()) {
                result = rs.getInt(1);
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            closeAll(rs, pst, con);
        }
        return result;
    }

    @Override
    public List<Paper> queryPaper(Paper paper, String startTime, String endTime, int page, int size) {
        List<Paper> list = new ArrayList<>();
        String sql = "select * from paper where 1=1";
        if (paper.getPaperTitle() != null && !"".equals(paper.getPaperTitle())) {
            sql += " and paper_title like '%" + paper.getPaperTitle() + "%' ";
        }
        if (paper.getPaperStatus() != null && !"".equals(paper.getPaperStatus())) {
            sql += " and paper_status=" + paper.getPaperStatus();
        }
        if (startTime != null && !"".equals(startTime)) {
            sql += " and paper_created > " + "'" + startTime + "'";
        }
        if (endTime != null && !"".equals(endTime)) {
            sql += " and paper_created < " + "'" + endTime + "'";
        }
        sql += " limit ?,?";

        try {
            con = getCon();
            pst = con.prepareStatement(sql);
            pst.setInt(1, (page - 1) * size);
            pst.setInt(2, size);
            rs = pst.executeQuery();
            while (rs.next()) {
                paper = new Paper();
                paper.setPaperId(rs.getLong("paper_id"));
                paper.setPaperTitle(rs.getString("paper_title"));
                paper.setPaperCreated(rs.getString("paper_created"));
                paper.setPaperDesc(rs.getString("paper_desc"));
                paper.setPaperStatus(rs.getInt("paper_status"));
                paper.setPaperCount(rs.getInt("paper_count"));
                paper.setAdminId(rs.getLong("admin_id"));
                list.add(paper);
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            closeAll(rs, pst, con);
        }
        return list;
    }

    @Override
    public int queryTotal(Paper paper, String startTime, String endTime) {
        String sql = "select count(*) from paper where 1=1";
        if (paper.getPaperTitle() != null && !"".equals(paper.getPaperTitle())) {
            sql += " and paper_title like '%" + paper.getPaperTitle() + "%' ";
        }
        if (paper.getPaperStatus() != null && !"".equals(paper.getPaperStatus())) {
            sql += " and paper_status=" + paper.getPaperStatus();
        }
        if (startTime != null && !"".equals(startTime)) {
            sql += " and paper_created > " + "'" + startTime + "'";
        }
        if (endTime != null && !"".equals(endTime)) {
            sql += " and paper_created < " + "'" + endTime + "'";
        }
        int result = -1;
        try {
            con = getCon();
            pst = con.prepareStatement(sql);
            rs = pst.executeQuery();
            if (rs.next()) {
                result = rs.getInt(1);
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            closeAll(rs, pst, con);
        }
        return result;
    }

    @Override
    public Paper queryById(Long paperId) {

        String sql = "select * from paper where paper_id=?";
        try {
            con = getCon();
            pst = con.prepareStatement(sql);
            pst.setLong(1, paperId);
            rs = pst.executeQuery();
            if (rs.next()) {
                Paper p = new Paper();
                p.setPaperCreated(rs.getString("paper_created"));
                p.setPaperDesc(rs.getString("paper_desc"));
                p.setPaperTitle(rs.getString("paper_title"));
                p.setPaperId(rs.getLong("paper_id"));
                p.setPaperCount(rs.getInt("paper_count"));
                p.setPaperStatus(rs.getInt("paper_status"));
                p.setAdminId(rs.getLong("admin_id"));
                return p;
            }

        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            closeAll(rs, pst, con);
        }
        return null;
    }

    @Override
    public int delte(Long paperId) {
        int result = -1;
        try {
            con = getCon();
            String sql = "delete from paper where paper_id=?";
            pst = con.prepareStatement(sql);
            pst.setLong(1, paperId);
            result = pst.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            closeAll(rs, pst, con);
        }
        return result;
    }

    @Override
    public int updateStatus(Long paperId,Integer status) {
        int result = -1;
        String sql = "update paper set paper_status=? where paper_id=?";
        try {
            con = getCon();
            pst = con.prepareStatement(sql);
            pst.setInt(1,status);
            pst.setLong(2, paperId);
            result = pst.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            closeAll(rs, pst, con);
        }
        return result;
    }

    /*修改问卷提交人数 */
    @Override
    public void addSubCount(long paperId, int i) {
        String sql = "update paper set paper_count = paper_count + ? " + " where paper_id = ?";
        try {
            con = getCon();
            pst = con.prepareStatement(sql);
            pst.setInt(1, i);
            pst.setLong(2, paperId);
            pst.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            closeAll(rs, pst, con);
        }
    }

}
